const express = require('express')
const router = express.Router()
const pool = require('../utils/pool')

// 查询房屋信息
router.get('/house', (req, res, next) => {
  const start = req.query.start || 1
  const count = Number(req.query.count) || 10
  const zh_properties = req.query.zh_properties || ''
  const area = req.query.area || ''
  const decoration = req.query.decoration || ''
  const house_type = req.query.house_type || ''
  let where = ''
  if (zh_properties === '' && area === '' && decoration === '' && house_type === '') {
    where = 1
  } else {
    //   where = `
    //   (IFNULL(property_type = '${zh_properties}', 1) = 1 OR property_type IS NULL)
    //   AND (IFNULL(area = '${area}', 1) = 1 OR area IS NULL)
    //   AND (IFNULL(decoration = '${decoration}', 1) = 1 OR decoration IS NULL)
    //   AND (IFNULL(house_type = '${house_type}', 1) = 1 OR house_type IS NULL)
    // `;
    where = `(CASE 
    WHEN '${zh_properties}' = '' THEN 1 
    WHEN property_type IS NULL THEN 1
    ELSE property_type = '${zh_properties}'
  END) = 1
  AND (CASE 
    WHEN '${area}' = '' THEN 1 
    WHEN area IS NULL THEN 1
    ELSE area = '${area}'
  END) = 1
  AND (CASE 
    WHEN '${decoration}' = '' THEN 1 
    WHEN decoration IS NULL THEN 1
    ELSE decoration = '${decoration}'
  END) = 1
  AND (CASE 
    WHEN '${house_type}' = '' THEN 1 
    WHEN house_type IS NULL THEN 1
    ELSE house_type = '${house_type}'
  END) = 1`
  }
  const sql = `
  SELECT * FROM zh_properties WHERE ${where} LIMIT ?, ?;
  SELECT COUNT(*) AS count FROM zh_properties WHERE ${where};
  SELECT DISTINCT property_type AS value FROM zh_properties ORDER BY property_type ASC;
  SELECT DISTINCT area AS value FROM zh_properties ORDER BY area ASC;
  SELECT DISTINCT decoration AS value FROM zh_properties ORDER BY decoration ASC;
  SELECT DISTINCT house_type AS value FROM zh_properties ORDER BY house_type ASC;
`;
  // pool.query('select * from zh_properties where ? limit ?, ?; select count(*) count from zh_properties; select distinct property_type value from zh_properties order by property_type asc; select distinct area value from zh_properties order by area asc; select distinct decoration value from zh_properties order by decoration asc; select distinct house_type value from zh_properties order by house_type', [where, (start - 1) * count, count], (err, result) => {
  pool.query(sql, [(start - 1) * count, count], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.length === 0) {
      return res.send({
        code: 4001,
        msg: '暂无房源信息'
      })
    }
    res.send({
      code: 2000,
      msg: 'success',
      data: {
        data: result[0],
        total: result[1][0].count,
        type: result[2],
        area: result[3],
        decoration: result[4],
        house_type: result[5],
        current: start,
        size: count
      }
    })
  })
})

// 查询某房屋信息
router.get('/house/:id', (req, res, next) => {
  const id = req.params.id
  pool.query('select * from zh_properties where id = ?', [id], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.length === 0) {
      return res.send({
        code: 4002,
        msg: '暂无该房源信息'
      })
    }
    res.send({
      code: 2000,
      msg: 'success',
      data: result[0]
    })
  })
})

// 删除某条房屋信息
router.delete('/house/:id', (req, res, next) => {
  const id = req.params.id
  pool.query('delete from zh_properties where id = ?', [id], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.affectedRows === 0) {
      return res.send({
        code: 4003,
        msg: '删除失败'
      })
    }
    res.send({
      code: 2000,
      msg: 'success'
    })
  })
})

// 添加房源信息
router.post('/addhouse', (req, res, next) => {
  req.body.size = parseFloat(req.body.size).toFixed(2)
  if (isNaN(req.body.size)) {
    return res.send({
      code: 4006,
      msg: '面积格式需要为数字'
    })
  }
  pool.query('insert into zh_properties set ?', [req.body], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.affectedRows === 0) {
      return res.send({
        code: 4004,
        msg: '添加失败'
      })
    }
    res.send({
      code: 2000,
      msg: 'success'
    })
  })
})

// 修改房源信息
router.put('/update/:id', (req, res, next) => {
  const id = req.params.id
  req.body.size = parseFloat(req.body.size).toFixed(2)
  if (isNaN(req.body.size)) {
    return res.send({
      code: 4006,
      msg: '面积格式需要为数字'
    })
  }
  pool.query('update zh_properties set ? where id = ?', [req.body, id], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.changedRows === 0) {
      return res.send({
        code: 4005,
        msg: '修改失败'
      })
    }
    res.send({
      code: 2000,
      msg: 'success'
    })
  })
})

// 查询全部设备信息
router.get('/devices', (req, res, next) => {
  const start = req.query.start || 1
  const count = Number(req.query.count) || 10
  pool.query('select * from zh_devices limit ?, ?; select count(*) count from zh_devices', [(start - 1) * count, count], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.length === 0) {
      return res.send({
        code: 4001,
        msg: '暂无设备信息'
      })
    }
    res.send({
      code: 2000,
      msg: 'success',
      data: result[0],
      total: result[1][0].count,
      current: start,
      size: count
    })
  })
})

// 获取所有设备的类型
router.get('/devices/type', (req, res, next) => {
  pool.query('select distinct type value from zh_devices', (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.length === 0) {
      return res.send({
        code: 4001,
        msg: '暂无设备信息'
      })
    }
    res.send({
      code: 2000,
      msg: 'success',
      data: result
    })
  })
})

// 获取指定设备类型
router.get('/devices/type/byid', (req, res, next) => {
  const start = req.query.start || 1
  const count = Number(req.query.count) || 10
  const type = req.query.type
  pool.query('select * from zh_devices where type = ? limit ?, ?; select count(*) count from zh_devices where type = ?', [type, (start - 1) * count, count, type], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.length === 0) {
      return res.send({
        code:4001,
        msg: '暂无该类型设备信息'
      })
    }
    res.send({
      code: 2000,
      msg: 'success',
      data: result[0],
      total: result[1][0].count,
      current: start,
      size: count
    })
  })
})

// 模糊查询设备编号
router.get('/devices/byid', (req, res, next) => {
  const start = req.query.start || 1
  const count = Number(req.query.count) || 10
  const id = req.query.id
  pool.query('select * from zh_devices where device_id like ? limit ?, ?; select count(*) count from zh_devices where device_id like ?', ['%' + id + '%', (start - 1) * count, count, '%' + id + '%',], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.length === 0) {
      return res.send({
       code: 4002,
       msg: '暂无该设备信息'
      })
    }
    res.send({
      code: 2000,
      msg: 'success',
      data: result[0],
      total: result[1][0].count,
      current: start,
      size: count
    })
  })
})

// 删除设备信息
router.delete('/devices/:id', (req, res, next) => {
  const id = req.params.id
  pool.query('delete from zh_devices where id = ?', [id], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.affectedRows === 0) {
      return res.send({
        code: 4003,
        msg: '删除失败'
      })
    }
    res.send({
      code: 2000,
      msg: 'success'
    })
  })
})

// 获取指定id的设备信息
router.get('/devices/:id', (req, res, next) => {
  const id = req.params.id
  pool.query('select * from zh_devices where id = ?', [id], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.length === 0) {
      return res.send({
        code: 4002,
        msg: '暂无该设备信息'
      })
    }
    res.send({
      code: 2000,
      msg: 'success',
      data: result[0]
    })
  })
})

// 修改设备信息
router.put('/update/devices/:id', (req, res, next) => {
  const id = req.params.id
  pool.query('update zh_devices set ? where id = ?', [req.body, id], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.changedRows === 0) {
      console.log(req.params.id)
      console.log(req.body)
      return res.send({
        code: 4005,
        msg: '修改失败'
      })
    }
    res.send({
      code: 2000,
      msg: 'success'
    })
  })
})

// 添加一条设备
router.post('/add/devices', (req, res, next) => {
  pool.query('insert into zh_devices set ?', [req.body], (err, result) => {
    if (err) {
      return next(err)
    }
    if (result.affectedRows === 0) {
      return res.send({
        code: 4004,
        msg: '添加失败'
      })
    }
    res.send({
      code: 2000,
      msg: 'success'
    })
  })
})

module.exports = router
